package cn.edu.zhku.citrusmonitor.qa.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import cn.edu.zhku.citrusmonitor.domain.TbAnswer;
import cn.edu.zhku.citrusmonitor.domain.TbQuestion;
import cn.edu.zhku.citrusmonitor.util.C3P0Util;

public class QuestionDaoImpl implements QuestionDao {
	
		BeanProcessor bean = new GenerousBeanProcessor();
		RowProcessor processor = new BasicRowProcessor(bean);

	@Override
	public void addQ(TbQuestion question) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		/* 
         *  
         * private String id; private String uuidname; //上传文件的名称，文件的uuid名 
         * private String filename; //上传文件的真实名称 private String savepath; 
         * //记住文件的位置 private Date uploadtime; //文件的上传时间 private String 
         * description; //文件的描述 private String username; 
         */  
        Object[] param = { question.getQuestionId(),question.getUserId(),question.getPresenter(),question.getSummary(),question.getContent(),question.getFilePath() }; 
       
            qr.update("insert into question(question_id,user_id,presenter,summary,content,file_path) values(?,?,?,?,?,?)", param);  
        
		
	}

	@Override
	public int removeQ(String questionId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="delete from question where question_id=?";
		return qr.update(sql,questionId);
	}

	@Override
	public int modifyQ(TbQuestion question) throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		Object[] param = { question.getSummary(),question.getContent(),question.getFilePath(),question.getQuestionId() }; 
    
            return qr.update("update question set summary=?,content=?,file_path=? where question_id=?", param);  
       
	}
	//Ajax搜索框显示数据，显示所有问题
	@Override
	public List<TbQuestion> findQAByWord(String content) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		
		String sql="select *from question where summary like? or content like?";

		return qr.query(sql,new BeanListHandler<TbQuestion>(TbQuestion.class,processor),"%"+content+"%","%"+content+"%");
	}
	
	//查找全部的数量
	@Override
	public int count() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="select count(*) from question";
		long l = qr.query(sql, new ScalarHandler<Long>(1));
		return (int)l;
	}
	
	
	
	//查找已经回复的问题数量
	@Override
	public int count(String status) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="";
		if(status.equals("已回复")) {
			sql="select count(*) from question where status='已回复'";
		}
		else if(status.equals("未回复")) {
			sql="select count(*) from question where status='未回复'";
		}
		long l = qr.query(sql, new ScalarHandler<Long>(1));
		return (int)l;
		
	}
	//显示问题
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List<TbQuestion> showQ(int currentPage, int pageSize,String status) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		
		String sql="";
		if(status.equals("已回复")) {
			sql="select question_id,create_time,summary,content,file_path from question where status='已回复'";
		}
		else if(status.equals("未回复")) {
			sql="select question_id,create_time,summary,content,file_path from question where status='未回复'";
		}
				
		List list = new ArrayList();
		
		sql+=" order by create_time desc";
		sql+=" limit ?,?";
		
		list.add((currentPage-1)*pageSize);
		list.add(pageSize);

		return  qr.query(sql,new BeanListHandler<TbQuestion>(TbQuestion.class,processor),list.toArray());
	
	}
	
	//根据questionId查找对应的回答
		@Override
		public TbAnswer findAByQid(String questionId) throws SQLException {
			QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
			
			String sql = "select content,answer_id from answer where question_id=?";
			
			return qr.query(sql, new BeanHandler<TbAnswer>(TbAnswer.class,processor),questionId);
		}


	//根据questionId查找对应的问题
	@Override
	public TbQuestion findQyByQid(String questionId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from question where question_id=?";
		TbQuestion question =qr.query(sql, new BeanHandler<TbQuestion>(TbQuestion.class,processor),questionId);
		
		//浏览一次BrowseCount就增加1
		long bc=question.getBrowseCount();
		bc+=1;
		sql="update question set browse_count=? where question_id=?";
		qr.update(sql,bc,questionId);			
		
		return question;
	}


	@Override
	public List<TbQuestion> showQAll(int pageSize) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		
		String sql = "select question_id,summary,status from question order by create_time desc limit 0,?";

		return  qr.query(sql,new BeanListHandler<TbQuestion>(TbQuestion.class,processor),pageSize);
	}

	@Override
	public String findUserPasswordById(String userId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		
		String sql = "select password from user where user_id=?";

		return  qr.query(sql,new ScalarHandler<>(1),userId);
		
	}

	@Override
	public List<TbQuestion> showAll(int currentPage, int pageSize) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from question order by create_time limit ?,?";
		

		return  qr.query(sql, new BeanListHandler<TbQuestion>(TbQuestion.class,processor),(currentPage-1)*pageSize,pageSize);
	}

	@Override
	public int delAll(String[] ids) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		Object[][] params = new Object[ids.length][];
		for (int i = 0; i < params.length; i++) {
			params[i] = new Object[]{ids[i]};//循环给每个一维数组中的元素赋值，值是id
		}
		qr.batch("delete from question where question_id=?", params);
		
		return 1;
	}


}
